Visual Basic for Applications (VBA) Procedures

A Sub procedure is a series of Visual Basic statements that perform a specific task. It can take arguments, such as constants, variables, or expressions that are passed by a calling the procedure. If a Sub procedure has no arguments, the Sub statement must include an empty set of parentheses.

A Sub procedure begins with a Sub statement, followed by the tasks to be performed, and ends with an End Sub statement. The following snippet of VBA code represents the structure of a Sub procedure:

Sub [ProcedureName] (Arguments)
[Statements]
End Sub

OneStream Functions are used with Sub procedures to automate data submission from XFSetCells formulas.

NOTE: To use these functions in Excel, you must have the latest version of OneStream and its corresponding Excel add-in installed.

Procedures currently supported are:

• LogonAndOpenApplication - Login varies depending on the authentication type setup for your application:

  NOTE: Resource Owner Password Credentials (ROPC) is no longer supported. As a result, the following ProcessSSOAuthenticationAndCreateToken has been deprecated. See Legacy Single Sign On (SSO) for Bearer Token procedure.
  

  Copy

  Sub SSOLogon()
  Set xfAddIn = Application.COMAddIns("OneStreamExcelAddIn")
  If Not xfAddIn Is Nothing Then
  If Not xfAddIn.Object Is Nothing Then
  ssoToken = xfAddIn.Object.ProcessSSOAuthenticationAndCreateToken
  ("https://golfstream.onestreamtest.com/OneStreamWeb",
  "user1@mycompany.com", "P@$$w0&D")
  xfUserName = xfAddIn.Object.GetXFUserNameFromSSOToken(ssoToken)

  • OneStream IdentityServer (OIS) - (url, PAT, application)
    Supports the use of Personal Access Tokens. Refer to the Identity and Access Management Guide for creating and managing PATS.

    Copy

    Sub OIS_PAT_Logon()
        Set xfAddIn = Application.COMAddIns("OneStreamExcelAddIn")
        If Not xfAddIn Is Nothing Then
            If Not xfAddIn.Object Is Nothing Then
                ' OIS using PAT
                loggedIn = xfAddIn.Object.LogonAndOpenApplicationWithToken("https://yoursite.onestreamcloud.com/OneStreamWeb", InsertPersonalAccessToken, "GolfStreamDemo_v36")                                                
                MsgBox ("Is Logged In : " & loggedIn)
            End If
        End If
    End Sub

  • Legacy Single Sign On (SSO) - (url, ssoToken, application)
    Supports the use of Bearer token from your identity provider

    Copy

    Sub SSOLogon()
        Set xfAddIn = Application.COMAddIns("OneStreamExcelAddIn")
        If Not xfAddIn Is Nothing Then
            If Not xfAddIn.Object Is Nothing Then
               ' Legacy SSO via Bearer Token
                Dim ssoToken As String
                ssoToken = <obtain Bearer token from your Identity Provider>"
                loggedIn = xfAddIn.Object.LogonAndOpenApplicationWithToken("https://yoursite.onestreamcloud.com/OneStreamWeb", ssoToken, "GolfStreamDemo_v36")
                MsgBox ("Is Logged In : " & loggedIn)
            End If
        End If
    End Sub

  • Native Authentication (Self-hosted) - (url, user, password, application)
    Support login via userid and password
    

    Copy

    Sub Native_Logon()
        Set xfAddIn = Application.COMAddIns("OneStreamExcelAddIn")
        If Not xfAddIn Is Nothing Then
            If Not xfAddIn.Object Is Nothing Then
                ' Native - UserName & Password
                loggedIn = xfAddIn.Object.LogonAndOpenApplication("https://yoursite.onestreamcloud.com/OneStreamWeb", UserName, Password, "GolfStreamDemo_v36")
                MsgBox ("Is Logged In ): " & loggedIn)
            End If
        End If
    End Sub

• Logoff()

• RefreshXFFunctions() -- refer to the following example:

  Copy

  Sub RefreshXFFunctions()
  Set xfAddIn = Application.COMAddIns("OneStreamExcelAddIn")
  If Not xfAddIn Is Nothing Then
  If Not xfAddIn.Object Is Nothing Then
  Call xfAddIn.Object.RefreshXFFunctions
  End If
  End If
  End Sub
  

• RefreshXFFunctionsForActiveWorksheet()

• RefreshQuickViews()

• RefreshQuickViewsForActiveWorksheet()

• RefreshCubeViews()

• RefreshCubeViewsForActiveWorkSheet()

• ShowParametersDlg()

• ShowParametersDlgForActiveWorksheet()

• SubmitXFFunctions () -- Automates the data loading process and eliminates the need to open the Excel files individually and submit data manually. Using a VBA routine, files with XFSET functions that are linked to other cells, sheets, and files can be programmatically submitted to OneStream. This procedure calls only XFSetCells. Refer to the following example:

  Copy

  Sub SubmitXFFunctionsTest()
      Set xfAddin = Application.COMAddIns("OneStreamExcelAddin") 
      If Not xfAddin Is Nothing Then                             
          If Not xfAddin.Object Is Nothing Then
              Call xfAddin.Object.SubmitXFFunctions              
              Call xfAddin.Object.RefreshXFFunctions             
          End If
      End If
  End Sub